package com.bitBusy.manageyourriches.database.queries;


import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteException;

import com.bitBusy.manageyourriches.database.contactClasses.ExpenseDetails;
import com.bitBusy.manageyourriches.database.managers.DatabaseOpeners;
import com.bitBusy.manageyourriches.database.managers.DateTimeManager;
import com.bitBusy.manageyourriches.database.tables.BasicDetails;
import com.bitBusy.manageyourriches.utilityClasses.dialogboxes.DialogBoxPrompts;
import com.bitBusy.manageyourriches.utilityClasses.display.DisplayMessages;
import com.bitBusy.manageyourriches.utilityClasses.filehandlers.ReportFileBuilder;

/** Class that holds all queries related to the BasicDetails table. Subclass of the DatabaseQueries class
 * @extends the DatabaseQueries class
 * @implements the UpdateSubcategories interface*/
public class QueriesBasicDetailsTable extends DatabaseQueries implements DatabaseOpeners {
	
	//MAY NEED TO ADD MORE QUERIES TO CLASS
	
	/** Reference to class of table related to queries in this class*/
	private static final String tableName = BasicDetails.class.getSimpleName();	
	
	/** String for the category column name*/
	private static final String categoryCol = BasicDetails.CATEGORYCOL.getName();
	
	/** String for the subcategory column name*/
	private static final String subCategoryCol = BasicDetails.SUBCATEGORYCOL.getName();
	
	/** String for the date column name*/
	private static final String dateCol = BasicDetails.DATECOL.getName();
	
	/** String for the time column name*/
	private static final String timeCol = BasicDetails.TIMECOL.getName();
	
	/** Day col*/
	private static final String dayCol = BasicDetails.DAYCOL.getName();
	
	/**String for payment info column name*/
	private static final String paymentInfoCol = BasicDetails.PAIDBYCOL.getName();
	
	/** String for the amount column name*/
	private static final String amountCol = BasicDetails.AMOUNTCOL.getName();
	
	/** String for the note column name*/
	private static final String noteCol = BasicDetails.NOTECOL.getName();
	
	/**String for FROM TABLE*/
	private static final String FROMTABLE = " FROM " + tableName;
	
	/** string to append to year*/
	private static final String YEARSTART = "0101";
	
	/** end of year*/
	private static final String YEAREND = "1231";
	
	/** string to append to month*/
	private static final String MONTHSTART = "01";
	
	/** string to append to month end*/
	private static final String MONTHEND = "31";
	
	/** to */
	private static final String TO = " - ";
	/** month mapping*/
	private static final String[] MONTHS = {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October",
											"November", "December"};
	/** time mapping*/
	private static final String[] TIMES = {"00:00:00", "01:00:00", "02:00:00", "03:00:00", "04:00:00", "05:00:00", "06:00:00", "07:00:00", 
		"08:00:00", "09:00:00", "10:00:00", "11:00:00", "12:00:00", "13:00:00", "14:00:00", "15:00:00", "16:00:00", "17:00:00", "18:00:00",
		"19:00:00", "20:00:00", "21:00:00", "22:00:00", "23:00:00", "00:00:00"};

	
	/** String array of all column names, in this table*/
	private static final String[] allColumns = new String[]{subCategoryCol, categoryCol, dateCol, timeCol, dayCol, paymentInfoCol, amountCol, noteCol};	

	/** Reference to the Cursor object used to store and process query results*/
	private Cursor cursor;
	

	/** Parameterized constructor - calls the superclass constructor
	 * @param the_context db context
	 * @param the activity context
	 */
	public QueriesBasicDetailsTable(Context the_context) {
			super(the_context);
	}
	
	/** Method to add a row of entry into the table
	 * @param the subcategory name
	 * @param the category name
	 * @param the date
	 * @param the time
	 * @param the method of payment
	 * @param the amount
	 * @param note, if any
	 * @return a long value indicating row entries were added to*/
	public long addRow(String the_subcategory, String the_category, ExpenseDetails the_expense)
	{
		long insertReturn = -1;
		boolean iOpened = checkAndOpen();

		int date = the_expense.getDateTime()!=null?DateTimeManager.getDate(the_expense.getDateTime()):null;
		String time = the_expense.getDateTime()!=null?DateTimeManager.getTime(the_expense.getDateTime()):null;
		String paymentInfo = the_expense.getPaymentMethod();
		String amount = String.valueOf(the_expense.getAmount());
		String note = the_expense.getNote();
		try
		{
			ContentValues contentInsert = new ContentValues();
			contentInsert.put(subCategoryCol, the_subcategory);
			contentInsert.put(categoryCol, the_category);
			contentInsert.put(dateCol, date);
			contentInsert.put(timeCol, time);
			contentInsert.put(dayCol, DateTimeManager.getDay(date));
			contentInsert.put(paymentInfoCol, paymentInfo);
			contentInsert.put(amountCol, amount);
			contentInsert.put(noteCol, note);
			insertReturn = myDatabase.insert(tableName, null, contentInsert);
		}
		catch(SQLiteException sqlex)
		{
			DisplayMessages.displayUserError(myActivityContext, DialogBoxPrompts.SQLINSERTTEXCEPTIONPROMPT.getPrompt());
		}
		finally
		{
			checkAndClose(iOpened);
		}
		return insertReturn;
		
	}
	
	/**Method to get total amount spent for asked duration
	 * @param from date
	 * @param to date
	 * @return double value of amount
	 */
	public double getTotalSpent(int the_fromDate, int the_toDate)
	{
		double sum = 0;
		
		//	DateComparator dateCompare = new DateComparator();
			boolean iOpened = checkAndOpen();
			cursor = myDatabase.rawQuery(getSelectSumQuery(amountCol) + FROMTABLE +" WHERE "
			+ dateCol + " >= " +  the_fromDate + " AND " + dateCol + " <= "+ the_toDate, null);
			if (cursor != null && cursor.moveToFirst())
			{
				sum = cursor.getDouble(0);
				cursor.close();
			}
			checkAndClose(iOpened);
		
		return sum;

	}
	
	/**Method to get total amount spent for asked duration for category
	 * @param category name
	 * @param from date
	 * @param to date
	 * @return double value of amount
	 */
	public double getTotalSpentCategory(String the_categoryName, int the_fromDate, int the_toDate)
	{
		double sum = 0;
		if (the_categoryName != null)
			//&& the_fromDate != null && the_toDate != null)
		{
			boolean iOpened = checkAndOpen();

			cursor = myDatabase.rawQuery(getSelectSumQuery(amountCol) + FROMTABLE +" WHERE " + categoryCol + " = '" + the_categoryName + "' AND "
			 +dateCol + " >= " + the_fromDate + " AND " + dateCol + " <= "
			 +the_toDate , null);
			if (cursor != null && cursor.moveToFirst())
			{
				sum = cursor.getDouble(0);
				cursor.close();
			}
			checkAndClose(iOpened);
		}
		return sum; 

	}
	
	
	/**Method to get total amount spent for asked duration for subcategory
	 * @param category name
	 * @param subcategory name
	 * @param from date
	 * @param to date
	 * @return double value of amount
	 */
	public double getTotalSpentSubcategory(String the_categoryName, String the_subcategoryName, int the_fromDate, int the_toDate)
	{
		double sum = 0;
		if (the_categoryName != null)
		{
			boolean iOpened = checkAndOpen();
			cursor = myDatabase.rawQuery(getSelectSumQuery(amountCol) + FROMTABLE +" WHERE " + categoryCol + " = '" + the_categoryName + "' AND "
				+subCategoryCol + " = '" + the_subcategoryName + "' AND " +dateCol + " >= '" + the_fromDate + "' AND " + dateCol + " <= '" 
						+the_toDate +"'", null);
			if (cursor != null && cursor.moveToFirst())
			{
				sum = cursor.getDouble(0);
				cursor.close();
			}
			checkAndClose(iOpened);
		}
		return sum;

	}



	/**Method to get a select sum string back
	 * @param the column to select sum over
	 * @return string
	 */
	private String getSelectSumQuery(String the_col)
	{
		if (the_col != null)
		{
			return "SELECT SUM("+the_col+")";
		}
		return null;
	}

	/**
	 * Method to get a list of expensedetails for a given subcategory
	 * @param the_parentCategory
	 * @param the_subcategory
	 * @param parseDBDate
	 * @param parseDBDate2
	 * @return
	 */
	public List<ExpenseDetails> getExpenses(String the_parentCategory,
			String the_subcategory, int the_fromDate, int the_toDate) {
		
		if (the_parentCategory != null && the_subcategory != null)
		{
			List<ExpenseDetails> expenses = null;
			boolean iOpened = checkAndOpen();
			cursor = myDatabase.rawQuery("SELECT "+ dateCol + " , " + timeCol + " , " + paymentInfoCol + " , " + amountCol + " , " + noteCol + " "
					+ FROMTABLE + " WHERE " + categoryCol + " = ? " + " AND " + subCategoryCol + " = ?", new String[]{the_parentCategory, the_subcategory});
			
			if (cursor != null && cursor.moveToFirst())
			{
				String dateTime;
				expenses = new ArrayList<ExpenseDetails>();
				while (!cursor.isAfterLast())
				{
					dateTime = DateTimeManager.getDateTime(cursor.getInt(cursor.getColumnIndex(dateCol)),
									cursor.getString(cursor.getColumnIndex(timeCol)));
					ExpenseDetails expense = getExpenseObject(dateTime,
															 cursor.getString(cursor.getColumnIndex(paymentInfoCol)), 
															 cursor.getDouble(cursor.getColumnIndex(amountCol)), 
															 cursor.getString(cursor.getColumnIndex(noteCol)));	
					expenses.add(expense);
					cursor.moveToNext();
				}
				cursor.close();
			}
			checkAndClose(iOpened);
			return expenses;
		}
		return null;
	}

	/**
	 * Method that returns an expensedetails object
	 * @param string
	 * @param string2
	 * @param double1
	 * @param string3
	 * @return
	 */
	private ExpenseDetails getExpenseObject(String the_date, String the_paymentInfo,
			double the_amount, String the_note) {
		return new ExpenseDetails(the_date, the_paymentInfo, the_amount, the_note);
	}
	

	
	public boolean deleteRecord(String the_parentCategory, String the_subcategory, ExpenseDetails the_expense)
	{
		boolean success = false;
		if (the_expense != null)
		{
			boolean iOpened = checkAndOpen();
			
			String dateTime = the_expense.getDateTime();
			int date = DateTimeManager.getDate(dateTime);
			String time = DateTimeManager.getTime(dateTime);
			
			if( myDatabase.delete(tableName, categoryCol + " = '" + the_parentCategory +"' AND " +
										subCategoryCol + " = '" + the_subcategory + "' AND " + 
										dateCol + " ='" + date + "' AND " + 
										timeCol + " ='" + time+"'", null)  == 1)
			{
				success = true;
			}
		checkAndClose(iOpened);
		}
		return success;
	}

	/**
	 * Returns total spent by a payment method for a period of time
	 * @param the_methodName
	 * @param the_fromDate
	 * @param the_toDate
	 * @return the sum
	 */
	public double getTotalSpentPaymentMethod(String the_methodName,
			int the_fromDate, int the_toDate) {
		
		double sum = 0;
		if (the_methodName != null)
		{
			boolean iOpened = checkAndOpen();

			cursor = myDatabase.rawQuery(getSelectSumQuery(amountCol) + FROMTABLE +" WHERE " + paymentInfoCol + " = '" + the_methodName + "' AND "
			 +dateCol + " >= " + the_fromDate + " AND " + dateCol + " <= "
			 +the_toDate , null);
			if (cursor != null && cursor.moveToFirst())
			{
				sum = cursor.getDouble(0);
				cursor.close();
			}
			checkAndClose(iOpened);
		}
		return sum; 

	}

	/**
	 * gets date wise spending
	 * @param the_fromDate
	 * @param the_toDate
	 * @return map of date and amount
	 */
	public LinkedHashMap<String, Double> getDatewiseSpending(int the_fromDate,
			int the_toDate) {
		
		LinkedHashMap<String, Double> dateAmtMap = null;
		ArrayList<Integer> dates = getDatesBetween(the_fromDate, the_toDate);
		
		if(dates != null)
		{	
			dateAmtMap = new LinkedHashMap<String, Double>();
			boolean iOpened = checkAndOpen();	
			for (int date: dates)
			{
				cursor = myDatabase.rawQuery(getSelectSumQuery(amountCol) + FROMTABLE +" WHERE " + dateCol + " = " + date,  null);
				if (cursor != null && cursor.moveToFirst())
					{
						dateAmtMap.put(DateTimeManager.parseForUserDate(date), cursor.getDouble(0));
						cursor.close();
					}
			}
		checkAndClose(iOpened);
		}
	return dateAmtMap; 	
	}

	/**
	 * gets all dates in db in this period
	 * @param the_fromDate
	 * @param the_toDate
	 * @return list of dates
	 */
	private ArrayList<Integer> getDatesBetween(int the_fromDate, int the_toDate) {
		
			ArrayList<Integer> returnDates = null;
			boolean iOpened = checkAndOpen();
			cursor = myDatabase.rawQuery("SELECT "+ dateCol +  " " + FROMTABLE + " WHERE " + 
					dateCol + " >= " + the_fromDate + " AND " + dateCol + " <= " + the_toDate, null);
			
			if (cursor != null && cursor.moveToFirst())
				{
				returnDates =  new ArrayList<Integer>();
					while (!cursor.isAfterLast())
					{
						returnDates.add(cursor.getInt(0));
						cursor.moveToNext();
					}
				cursor.close();
				}
			checkAndClose(iOpened);
			return returnDates;
		}

	/**
	 * @param the_fromYear
	 * @param the_toYear
	 * @return
	 */
	public LinkedHashMap<String, Double> getYearWiseSpending(int the_fromYear,
			int the_toYear) {
		
		LinkedHashMap<String, Double> yearAmtMap =  new LinkedHashMap<String, Double>();
		for (int i = the_fromYear; i <= the_toYear; i++)
		{
			yearAmtMap.put(String.valueOf(i), getTotalSpentYear(i));
		}
		
		return yearAmtMap;
	}
	
	/** method to get total spent for a given year
	 * @param the int year
	 * @return total spent double
	 */
	private double getTotalSpentYear(int the_year)
	{
		double sum = 0;
		String yearString = String.valueOf(the_year);
		String yearStart = yearString.concat(YEARSTART);
		String yearEnd = yearString.concat(YEAREND);
		boolean iOpened = checkAndOpen();	
		cursor = myDatabase.rawQuery(getSelectSumQuery(amountCol) + FROMTABLE +" WHERE " + dateCol + " >= " + Integer.parseInt(yearStart) 
				 + " AND " + dateCol + " <= " + Integer.parseInt(yearEnd),  null);
		if (cursor != null && cursor.moveToFirst())
		{
			sum = cursor.getDouble(0);
			cursor.close();
		}
		checkAndClose(iOpened);
		return sum;

	}

	/**
	 * @param the_fromMonth
	 * @param the_toMonth
	 * @return
	 */
	
	public LinkedHashMap<String, Double> getMonthwiseSpending(
			int the_fromMonth, int the_toMonth) {
		
		LinkedHashMap<String, Double> monthAmtMap =  new LinkedHashMap<String, Double>();
		int month;
		for (int i = the_fromMonth; i <= the_toMonth; i++)
		{
			month = getMonth(i);
			monthAmtMap.put(month >= 0? MONTHS[month-1]:"", getTotalSpentMonth(i));
		}		
		return monthAmtMap;
	}


	/**
	 * extracts month out of the guven date
	 * @param i date
	 * @return month
	 */
	private int getMonth(int the_date) {
		int extractCount = 2;
		int returnMonth = 0;
		int temp;
		while (the_date > 0 && extractCount > 0)
		{
			temp = the_date % 10;
			returnMonth = returnMonth>0?(temp*10 + returnMonth):temp;
			extractCount--;
			the_date = the_date/10;
		}
		
		return extractCount==0?returnMonth:-1;
	}
		
	/**
	 * gets total spent for months period
	 * @param the month
	 * @return total spent
	 */
	private Double getTotalSpentMonth(int the_month) {
		double sum = 0;
		String monthString = String.valueOf(the_month);
		String monthStart = monthString.concat(MONTHSTART);
		String monthEnd = monthString.concat(MONTHEND);
		boolean iOpened = checkAndOpen();	
		cursor = myDatabase.rawQuery(getSelectSumQuery(amountCol) + FROMTABLE +" WHERE " + dateCol + " >= " + Integer.parseInt(monthStart) 
				 + " AND " + dateCol + " <= " + Integer.parseInt(monthEnd),  null);
		if (cursor != null && cursor.moveToFirst())
		{
			sum = cursor.getDouble(0);
			cursor.close();
		}
		checkAndClose(iOpened);
		return sum;	}

	/**
	 * @param the_fromDate
	 * @param the_toDate
	 * @return
	 */
	public LinkedHashMap<String, Double> getDaywiseSpending(int the_fromDate,
			int the_toDate) {
		LinkedHashMap<String, Double> daywiseSpending = new LinkedHashMap<String, Double>();
		boolean iOpened = checkAndOpen();	
		
		for (int i = 0; i < DateTimeManager.DAYS.length; i++)
		{
			cursor = myDatabase.rawQuery
					(getSelectSumQuery(amountCol) + FROMTABLE +" WHERE " + dateCol + " >= " + the_fromDate + " AND " + dateCol + " <= "
									+ the_toDate + " AND " + dayCol + " ='" + DateTimeManager.DAYS[i] + "' ", null);
			if (cursor != null && cursor.moveToFirst())
			{
				daywiseSpending.put(DateTimeManager.DAYS[i], cursor.getDouble(0));
				cursor.close();
			}

		}
		
		checkAndClose(iOpened);
		return daywiseSpending;	
		}

	/**
	 * @param the_fromDate
	 * @param the_toDate
	 * @return
	 */
	public LinkedHashMap<String, Double> getTimewiseSpending(int the_fromDate,
			int the_toDate) {
		LinkedHashMap<String, Double> timeAmtMap =  new LinkedHashMap<String, Double>();
		for (int i = 0; i < TIMES.length - 1; i++)
		{
			timeAmtMap.put(TIMES[i].concat(TO).concat(TIMES[i+1]), getTotalSpentTime(TIMES[i], TIMES[i+1], the_fromDate, the_toDate));
		}		
		return timeAmtMap;
	}

	/**
	 * gets total spent for time period
	 * @param the_toDate 
	 * @param the_fromDate 
	 * @param the month
	 * @return total spent
	 */
	private Double getTotalSpentTime(String the_fromTime, String the_toTime, int the_fromDate, int the_toDate) {
		double sum = 0;
		
		if (the_fromTime != null && the_toTime != null)
		{
			boolean iOpened = checkAndOpen();	
			cursor = myDatabase.rawQuery(getSelectSumQuery(amountCol) + FROMTABLE +" WHERE " + dateCol + " >= " +  the_fromDate + 
					  " AND " + dateCol + " <= " + the_toDate + " AND " + timeCol + " >= '" + the_fromTime + "' AND " + 
					timeCol + " <= '" + the_toTime + "'",  null);
			if (cursor != null && cursor.moveToFirst())
			{
				sum = cursor.getDouble(0);
				cursor.close();
			}
			checkAndClose(iOpened);
		}
			return sum;	
		}

	/**
	 * @param the_fileBuilder 
	 * @return
	 */
	public void getReportTillDate(ReportFileBuilder the_fileBuilder) {
		 if (the_fileBuilder != null)
		 {
				boolean iOpened = checkAndOpen();	
			 the_fileBuilder.registerOpener(this, iOpened);
				the_fileBuilder.execute(getAllExpensesCursor());

				

		 }
	}

	/**
	 * @return
	 */
	private Cursor getAllExpensesCursor() {
		//boolean iOpened = checkAndOpen();	
		cursor = myDatabase.query(tableName,allColumns, null, null, null, null, null);
	//	checkAndClose(iOpened);
		return cursor;	

	}

	/* (non-Javadoc)
	 * @see com.bitBusy.manageyourriches.database.managers.DatabaseOpeners#closeDB(boolean)
	 */
	@Override
	public void closeDB(boolean the_iOpened) {
		checkAndClose(the_iOpened);
	}

	/**
	 * @param the_categoryChosen
	 */
	public boolean deleteCategory(String the_categoryChosen) {
		boolean success = false;
		if (the_categoryChosen != null)
		{
			boolean iOpened = checkAndOpen();
	    	if( myDatabase.delete(tableName, categoryCol + " = '" + the_categoryChosen +"'", null)  >= 0)
			{
				success = true;
			}
		checkAndClose(iOpened);
		}
		return success;
	}

	/**
	 * @param the_subcategory
	 * @param the_subcategory2 
	 * @return
	 */
	public boolean deleteSubcategory(String the_category, String the_subcategory) {
		boolean success = false;
		if (the_subcategory != null)
		{
			boolean iOpened = checkAndOpen();
	    	if( myDatabase.delete(tableName, categoryCol + " = '" + the_category + "' AND " + 
	    						subCategoryCol + " = '" + the_subcategory +"'", null)  >= 0)
			{
				success = true;
			}
		checkAndClose(iOpened);
		}
		return success;
		}

	

		

}
